USE <database_name>;
GO

-- list which tables are partitioned, and what partition scheme and partition function they use
SELECT
	t.name AS table_name,
	ps.name AS partition_scheme, 
	ps.data_space_id,
	pf.name AS partition_function,
	pf.function_id
FROM sys.tables t
	INNER JOIN sys.indexes i ON t.object_id = i.object_id
	INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
	INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id < 2;

-- for a given partition function, list each of the boundary values
SELECT
	prv.boundary_id,
	prv.value
FROM sys.partition_range_values prv
	INNER JOIN sys.partition_functions pf ON prv.function_id = pf.function_id
WHERE pf.name = '<partition_function_name>'
ORDER BY prv.value;